<!DOCTYPE html>
<html lang="zh-CN">
<head>
	<meta charset="UTF-8">
	<meta name="viewport" content="width=device-width, initial-scale=1, viewport-fit=cover">
	<title>SN码对比与修改</title>
	<style>
		:root {
			--primary: #ed781b;
			--primary-dark: #d66a17;
			--bg: #f7f8fa;
			--text: #1f2329;
			--muted: #8a8f98;
			--border: #e5e6eb;
			--success: #16a34a;
			--danger: #ef4444;
			--warning: #f59e0b;
		}
		* { box-sizing: border-box; }
		html, body { height: 100%; margin: 0; padding: 0; }
		body { background: var(--bg); color: var(--text); font-family: -apple-system, BlinkMacSystemFont, "SF Pro Text", "Segoe UI", Roboto, "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei", Helvetica, Arial, sans-serif; }

		.container { max-width: 1200px; margin: 0 auto; padding: 20px; }

		.page-header {
			background: linear-gradient(135deg, #2c3e50 0%, #34495e 100%);
			padding: 20px;
			border-radius: 12px;
			box-shadow: 0 3px 10px rgba(44, 62, 80, 0.3);
			margin-bottom: 20px;
		}
		.page-title {
			color: #fff;
			font-size: 24px;
			font-weight: 600;
			margin: 0;
			text-shadow: 0 1px 2px rgba(0,0,0,0.3);
		}

		.upload-section {
			background: #fff;
			border: 1px solid var(--border);
			border-radius: 12px;
			padding: 24px;
			box-shadow: 0 2px 8px rgba(0,0,0,0.05);
			margin-bottom: 20px;
		}
		.upload-section h2 {
			margin: 0 0 16px 0;
			font-size: 18px;
			color: var(--text);
		}
		.upload-area {
			border: 2px dashed var(--border);
			border-radius: 8px;
			padding: 40px;
			text-align: center;
			transition: all 0.3s ease;
			cursor: pointer;
		}
		.upload-area:hover {
			border-color: var(--primary);
			background: #fafafa;
		}
		.upload-area.dragover {
			border-color: var(--primary);
			background: #fff5f0;
		}
		.upload-icon {
			font-size: 48px;
			margin-bottom: 12px;
		}
		.upload-text {
			color: var(--muted);
			font-size: 14px;
			margin-bottom: 8px;
		}
		.upload-hint {
			color: var(--muted);
			font-size: 12px;
		}
		.file-input {
			display: none;
		}
		.upload-btn {
			margin-top: 16px;
			background: var(--primary);
			color: #fff;
			border: 0;
			border-radius: 8px;
			padding: 12px 24px;
			font-size: 14px;
			font-weight: 500;
			cursor: pointer;
			transition: all 0.2s ease;
		}
		.upload-btn:hover {
			background: var(--primary-dark);
		}
		.upload-btn:disabled {
			background: #ccc;
			cursor: not-allowed;
		}

		.result-section {
			background: #fff;
			border: 1px solid var(--border);
			border-radius: 12px;
			padding: 24px;
			box-shadow: 0 2px 8px rgba(0,0,0,0.05);
			display: none;
		}
		.result-section h2 {
			margin: 0 0 16px 0;
			font-size: 18px;
			color: var(--text);
		}
		.result-summary {
			display: flex;
			gap: 16px;
			margin-bottom: 20px;
			flex-wrap: wrap;
		}
		.summary-item {
			flex: 1;
			min-width: 150px;
			padding: 16px;
			background: #f7f8fa;
			border-radius: 8px;
			text-align: center;
		}
		.summary-label {
			font-size: 12px;
			color: var(--muted);
			margin-bottom: 8px;
		}
		.summary-value {
			font-size: 24px;
			font-weight: 600;
			color: var(--text);
		}
		.summary-value.success { color: var(--success); }
		.summary-value.danger { color: var(--danger); }
		.summary-value.warning { color: var(--warning); }

		.diff-table {
			width: 100%;
			border-collapse: collapse;
			margin-top: 20px;
		}
		.diff-table th,
		.diff-table td {
			padding: 12px;
			text-align: left;
			border-bottom: 1px solid var(--border);
		}
		.diff-table th {
			background: #f7f8fa;
			font-weight: 600;
			font-size: 14px;
			color: var(--text);
			position: sticky;
			top: 0;
		}
		.diff-table td {
			font-size: 13px;
		}
		.diff-table tr:hover {
			background: #fafafa;
		}
		.sn-list {
			max-width: 300px;
			word-break: break-all;
		}
		.sn-item {
			display: inline-block;
			margin: 2px 4px 2px 0;
			padding: 2px 6px;
			background: #f0f0f0;
			border-radius: 4px;
			font-size: 11px;
		}
		.sn-item.excel-only {
			background: #fee2e2;
			color: #991b1b;
		}
		.sn-item.db-only {
			background: #dbeafe;
			color: #1e40af;
		}

		.action-section {
			margin-top: 20px;
			text-align: center;
		}
		.execute-btn {
			background: var(--danger);
			color: #fff;
			border: 0;
			border-radius: 8px;
			padding: 14px 32px;
			font-size: 16px;
			font-weight: 500;
			cursor: pointer;
			transition: all 0.2s ease;
		}
		.execute-btn:hover {
			background: #dc2626;
		}
		.execute-btn:disabled {
			background: #ccc;
			cursor: not-allowed;
		}

		.loading {
			display: none;
			text-align: center;
			padding: 40px;
		}
		.loading.active {
			display: block;
		}
		.spinner {
			border: 3px solid #f3f3f3;
			border-top: 3px solid var(--primary);
			border-radius: 50%;
			width: 40px;
			height: 40px;
			animation: spin 1s linear infinite;
			margin: 0 auto 16px;
		}
		@keyframes spin {
			0% { transform: rotate(0deg); }
			100% { transform: rotate(360deg); }
		}

		.toast {
			position: fixed;
			left: 50%;
			bottom: 30px;
			transform: translateX(-50%);
			background: rgba(0,0,0,.85);
			color: #fff;
			padding: 12px 20px;
			border-radius: 25px;
			font-size: 14px;
			display: none;
			z-index: 9999;
			font-weight: 500;
			box-shadow: 0 4px 20px rgba(0,0,0,0.3);
		}
		.toast.show {
			display: block;
		}

		.error-message {
			background: #fee2e2;
			color: #991b1b;
			padding: 16px;
			border-radius: 8px;
			margin-bottom: 20px;
			border: 1px solid #fecaca;
		}

		.sql-preview {
			background: #f7f8fa;
			border: 1px solid var(--border);
			border-radius: 8px;
			padding: 16px;
			margin-top: 20px;
			display: none;
		}
		.sql-preview.active {
			display: block;
		}
		.sql-preview h3 {
			margin: 0 0 12px 0;
			font-size: 16px;
			color: var(--text);
		}
		.sql-list {
			background: #fff;
			border: 1px solid var(--border);
			border-radius: 6px;
			padding: 12px;
			max-height: 400px;
			overflow-y: auto;
			font-family: 'Courier New', monospace;
			font-size: 12px;
			line-height: 1.6;
		}
		.sql-item {
			margin-bottom: 16px;
			padding-bottom: 16px;
			border-bottom: 1px solid var(--border);
		}
		.sql-item:last-child {
			margin-bottom: 0;
			padding-bottom: 0;
			border-bottom: none;
		}
		.sql-item-header {
			font-weight: 600;
			color: var(--text);
			margin-bottom: 8px;
		}
		.sql-code {
			color: #1e40af;
			white-space: pre-wrap;
			word-break: break-all;
		}
		.confirm-buttons {
			margin-top: 16px;
			display: flex;
			gap: 12px;
			justify-content: center;
		}
		.confirm-btn {
			padding: 10px 24px;
			border: 0;
			border-radius: 6px;
			font-size: 14px;
			font-weight: 500;
			cursor: pointer;
			transition: all 0.2s ease;
		}
		.confirm-btn.primary {
			background: var(--danger);
			color: #fff;
		}
		.confirm-btn.primary:hover {
			background: #dc2626;
		}
		.confirm-btn.secondary {
			background: #e5e6eb;
			color: var(--text);
		}
		.confirm-btn.secondary:hover {
			background: #d1d5db;
		}
	</style>
</head>
<body>
	<div class="container">
		<div class="page-header">
			<h1 class="page-title">SN码对比与修改</h1>
		</div>

		<div class="upload-section">
			<h2>上传Excel文件</h2>
			<div class="upload-area" id="uploadArea">
				<div class="upload-icon">📄</div>
				<div class="upload-text">点击或拖拽文件到此处上传</div>
				<div class="upload-hint">支持 .xlsx, .xls 格式</div>
				<input type="file" id="fileInput" class="file-input" accept=".xlsx,.xls">
			</div>
			<div style="text-align: center; margin-top: 16px;">
				<button class="upload-btn" id="uploadBtn" disabled>开始处理</button>
			</div>
			<div class="upload-hint" style="margin-top: 16px; padding: 12px; background: #f7f8fa; border-radius: 8px;">
				<strong>Excel文件格式要求：</strong><br>
				必须包含以下列：客户单号、入库单号、入库行号、货主、商品代码、商品名称、收货库位、SN码<br>
				<strong>必填字段：</strong>客户单号、商品代码、SN码（任一为空将报错）
			</div>
		</div>

		<div class="loading" id="loading">
			<div class="spinner"></div>
			<div>正在处理中，请稍候...</div>
		</div>

		<div class="result-section" id="resultSection">
			<h2>对比结果</h2>
			<div id="errorMessage" class="error-message" style="display: none;"></div>
			<div class="result-summary" id="resultSummary"></div>
			<div style="overflow-x: auto;">
				<table class="diff-table" id="diffTable">
					<thead>
						<tr>
							<th>客户单号</th>
							<th>商品代码</th>
							<th>批次号</th>
							<th>Excel中的SN码</th>
							<th>数据库中的SN码</th>
							<th>差异说明</th>
						</tr>
					</thead>
					<tbody id="diffTableBody"></tbody>
				</table>
			</div>
			<div class="action-section">
				<button class="execute-btn" id="executeBtn" disabled>执行修改</button>
			</div>
			<div class="sql-preview" id="sqlPreview">
				<h3>将要执行的SQL语句：</h3>
				<div class="sql-list" id="sqlList"></div>
				<div class="confirm-buttons">
					<button class="confirm-btn primary" id="confirmExecuteBtn">确认执行</button>
					<button class="confirm-btn secondary" id="cancelExecuteBtn">取消</button>
				</div>
			</div>
		</div>
	</div>

	<div class="toast" id="toast"></div>

	<script>
		let selectedFile = null;
		let diffData = [];

		const uploadArea = document.getElementById('uploadArea');
		const fileInput = document.getElementById('fileInput');
		const uploadBtn = document.getElementById('uploadBtn');
		const loading = document.getElementById('loading');
		const resultSection = document.getElementById('resultSection');
		const errorMessage = document.getElementById('errorMessage');
		const resultSummary = document.getElementById('resultSummary');
		const diffTableBody = document.getElementById('diffTableBody');
		const executeBtn = document.getElementById('executeBtn');
		const toast = document.getElementById('toast');
		const sqlPreview = document.getElementById('sqlPreview');
		const sqlList = document.getElementById('sqlList');
		const confirmExecuteBtn = document.getElementById('confirmExecuteBtn');
		const cancelExecuteBtn = document.getElementById('cancelExecuteBtn');

		function showToast(msg) {
			toast.textContent = msg;
			toast.classList.add('show');
			setTimeout(() => {
				toast.classList.remove('show');
			}, 3000);
		}

		// 文件选择
		uploadArea.addEventListener('click', () => {
			fileInput.click();
		});

		fileInput.addEventListener('change', (e) => {
			if (e.target.files.length > 0) {
				selectedFile = e.target.files[0];
				uploadArea.querySelector('.upload-text').textContent = selectedFile.name;
				uploadBtn.disabled = false;
			}
		});

		// 拖拽上传
		uploadArea.addEventListener('dragover', (e) => {
			e.preventDefault();
			uploadArea.classList.add('dragover');
		});

		uploadArea.addEventListener('dragleave', () => {
			uploadArea.classList.remove('dragover');
		});

		uploadArea.addEventListener('drop', (e) => {
			e.preventDefault();
			uploadArea.classList.remove('dragover');
			if (e.dataTransfer.files.length > 0) {
				selectedFile = e.dataTransfer.files[0];
				fileInput.files = e.dataTransfer.files;
				uploadArea.querySelector('.upload-text').textContent = selectedFile.name;
				uploadBtn.disabled = false;
			}
		});

		// 上传处理
		uploadBtn.addEventListener('click', async () => {
			if (!selectedFile) {
				showToast('请先选择文件');
				return;
			}

			const formData = new FormData();
			formData.append('file', selectedFile);

			loading.classList.add('active');
			resultSection.style.display = 'none';
			uploadBtn.disabled = true;

			try {
				const response = await fetch('/upload/api/compareSn', {
					method: 'POST',
					body: formData
				});

				const result = await response.json();

				loading.classList.remove('active');
				uploadBtn.disabled = false;

				if (!response.ok) {
					errorMessage.textContent = result.error || result.msg || '处理失败';
					errorMessage.style.display = 'block';
					resultSection.style.display = 'block';
					return;
				}

				if (result.error) {
					errorMessage.textContent = result.error;
					errorMessage.style.display = 'block';
					resultSection.style.display = 'block';
					return;
				}

				// 显示结果
				diffData = result.differences || [];
				displayResults(result);
			} catch (error) {
				loading.classList.remove('active');
				uploadBtn.disabled = false;
				errorMessage.textContent = '处理失败: ' + error.message;
				errorMessage.style.display = 'block';
				resultSection.style.display = 'block';
				showToast('处理失败: ' + error.message);
			}
		});

		function displayResults(result) {
			errorMessage.style.display = 'none';
			resultSection.style.display = 'block';

			// 显示统计信息 - 只显示差异数
			const diffCount = diffData.length;

			resultSummary.innerHTML = '';
			if (diffCount > 0) {
				resultSummary.innerHTML = `
					<div class="summary-item">
						<div class="summary-label">有差异记录数</div>
						<div class="summary-value warning">${diffCount}</div>
					</div>
				`;
			}

			// 显示差异表格
			diffTableBody.innerHTML = '';
			if (diffData.length === 0) {
				diffTableBody.innerHTML = '<tr><td colspan="6" style="text-align: center; padding: 40px; color: var(--muted);">没有发现差异</td></tr>';
				executeBtn.disabled = true;
			} else {
				diffData.forEach(item => {
					const excelSns = item.excel_sns || [];
					const dbSns = item.db_sns || [];
					const excelOnly = item.excel_only || excelSns.filter(sn => !dbSns.includes(sn));
					const dbOnly = item.db_only || dbSns.filter(sn => !excelSns.includes(sn));

					// 只显示有差异的SN码
					const excelHtml = excelOnly.length > 0 
						? excelOnly.map(sn => `<span class="sn-item excel-only">${sn}</span>`).join('')
						: '<span style="color: var(--muted);">无差异</span>';

					const dbHtml = dbOnly.length > 0 
						? dbOnly.map(sn => `<span class="sn-item db-only">${sn}</span>`).join('')
						: '<span style="color: var(--muted);">无差异</span>';

					// 以数据差异数为准（只计算数据库中多出的SN码）
					const diffDesc = dbOnly.length > 0 ? `差异数,${dbOnly.length}个` : '';

					const row = document.createElement('tr');
					row.innerHTML = `
						<td>${item.trace_id || ''}</td>
						<td>${item.sku_code || ''}</td>
						<td>${item.lot_num || ''}</td>
						<td class="sn-list">${excelHtml}</td>
						<td class="sn-list">${dbHtml}</td>
						<td>${diffDesc}</td>
					`;
					diffTableBody.appendChild(row);
				});
				executeBtn.disabled = false;
			}
		}

		// 转义SQL字符串中的单引号
		function escapeSqlString(str) {
			return String(str).replace(/'/g, "''");
		}

		// 生成SQL语句预览
		function generateSqlPreview() {
			const sqls = [];
			
			diffData.forEach(item => {
				const traceId = item.trace_id || '';
				const skuCode = item.sku_code || '';
				const excelSns = item.excel_sns || [];
				const dbSns = item.db_sns || [];
				
				// 找出Excel中有但数据库中没有的SN码
				const excelOnly = excelSns.filter(sn => !dbSns.includes(sn));
				// 找出数据库中有但Excel中没有的SN码
				const dbOnly = dbSns.filter(sn => !excelSns.includes(sn));
				
				// 建立对应关系：将db_only中的SN码更新为excel_only中的SN码
				// 按顺序对应，取较小的数量
				const pairCount = Math.min(excelOnly.length, dbOnly.length);
				
				for (let i = 0; i < pairCount; i++) {
					const oldSn = dbOnly[i];  // 数据库中的旧SN码
					const newSn = excelOnly[i];  // Excel中的新SN码
					
					// 转义特殊字符，确保SQL显示正确
					const escapedTraceId = escapeSqlString(traceId);
					const escapedSkuCode = escapeSqlString(skuCode);
					const escapedOldSn = escapeSqlString(oldSn);
					const escapedNewSn = escapeSqlString(newSn);
					
					const sql = `UPDATE wm_so_serial 
SET SERIAL_NO = '${escapedNewSn}', 
    SCAN_OP = 'systemModiry', 
    SCAN_TIME = NOW() 
WHERE TRACE_ID = '${escapedTraceId}' 
    AND SKU_CODE = '${escapedSkuCode}' 
    AND SERIAL_NO = '${escapedOldSn}';`;
					
					sqls.push({
						traceId: traceId,
						skuCode: skuCode,
						oldSn: oldSn,
						newSn: newSn,
						sql: sql
					});
				}
			});
			
			return sqls;
		}

		// 显示SQL预览
		function showSqlPreview() {
			const sqls = generateSqlPreview();
			
			if (sqls.length === 0) {
				showToast('没有需要执行的SQL语句');
				return;
			}
			
			sqlList.innerHTML = '';
			sqls.forEach((item, index) => {
				const sqlItem = document.createElement('div');
				sqlItem.className = 'sql-item';
				sqlItem.innerHTML = `
					<div class="sql-item-header">SQL ${index + 1}: 客户单号=${item.traceId}, 商品代码=${item.skuCode}, 旧SN=${item.oldSn} → 新SN=${item.newSn}</div>
					<div class="sql-code">${item.sql}</div>
				`;
				sqlList.appendChild(sqlItem);
			});
			
			sqlPreview.classList.add('active');
			// 滚动到SQL预览区域
			sqlPreview.scrollIntoView({ behavior: 'smooth', block: 'nearest' });
		}

		// 执行修改
		executeBtn.addEventListener('click', () => {
			if (diffData.length === 0) {
				showToast('没有需要修改的数据');
				return;
			}
			
			// 显示SQL预览
			showSqlPreview();
		});

		// 确认执行
		confirmExecuteBtn.addEventListener('click', async () => {
			executeBtn.disabled = true;
			confirmExecuteBtn.disabled = true;
			cancelExecuteBtn.disabled = true;
			loading.classList.add('active');
			sqlPreview.classList.remove('active');

			try {
				const response = await fetch('/upload/api/executeSnUpdate', {
					method: 'POST',
					headers: {
						'Content-Type': 'application/json'
					},
					body: JSON.stringify({ differences: diffData })
				});

				const result = await response.json();
				loading.classList.remove('active');
				executeBtn.disabled = false;
				confirmExecuteBtn.disabled = false;
				cancelExecuteBtn.disabled = false;

				if (!response.ok || result.error) {
					showToast(result.error || result.msg || '执行失败');
					return;
				}

				showToast('修改执行成功');
				// 重新加载对比结果
				uploadBtn.click();
			} catch (error) {
				loading.classList.remove('active');
				executeBtn.disabled = false;
				confirmExecuteBtn.disabled = false;
				cancelExecuteBtn.disabled = false;
				showToast('执行失败: ' + error.message);
			}
		});

		// 取消执行
		cancelExecuteBtn.addEventListener('click', () => {
			sqlPreview.classList.remove('active');
		});
	</script>
</body>
</html>

